using System;
using System.Data;
using System.Text;
using System.Collections;
using System.Collections.Generic;

using BizElements.Core.ProviderComponents;

namespace BizElements.Core
{
    #region Documentation.
    /// <summary>
    /// Retrieves rows from the database and allows the selection of one or many rows or columns from one or many tables. 
    /// <seealso cref="SearchCondition"/><seealso cref="PredicateFactory"/><seealso cref="RelationBucket"/><seealso cref="AggregateFunction"/>
    /// </summary>
    /// <example>
    /// <para>
    /// The following example creates a SelectStatement and uses it to select territory and region descriptions. 
    /// Only the rows that match the given criteria (territory description has to start with a letter between the 
    /// two specified characters) are returned. Data is sorted ascending by territory description.
    /// <code>
    /// public DataTable SelectTerritoryAndRegionNames(int maxNumberOfRowsReturned, char beginLetter, char endLetter)
    /// {
    /// 	//	SELECT TOP 10 TerritoryDescription, RegionDescription 
    /// 	//	FROM Territories 
    /// 	//		INNER JOIN Region ON Region.RegionID = Territories.RegionID  
    /// 	//	WHERE TerritoryDescription BETWEEN @beginLetter AND @endLetter
    /// 	//	ORDER BY TerritoryDescription
    /// 
    /// 	TerritoriesMeta territories = new TerritoriesMeta();
    /// 	RegionMeta regions = new RegionMeta();
    /// 
    /// 	SelectStatement select = new SelectStatement(territories);
    /// 	select.Top = maxNumberOfRowsReturned;
    /// 	select.SelectList.Add(territories.TerritoryDescription, regions.RegionDescription);
    /// 	select.Relations.Add(regions, territories, territories.RegionID);
    /// 	select.Where.Add(PredicateFactory.Between(territories.TerritoryDescription, beginLetter, endLetter));
    /// 	select.OrderBy.Add(territories.TerritoryDescription);			
    /// 
    /// 	return select.Execute();
    /// }
    /// </code>
    /// </para>
    /// <para>
    /// The following example demonstrates how to use aggregate functions, sub-queries, GROUP BY and HAVING clauses.
    /// <code>
    /// private DataTable FetchImprovingCustomers97()
    /// {			
    /// 	//	Customers that spent more than $500 in 1997 but spent less than $100 in 1996.
    /// 
    /// 	//	SELECT Customers.CompanyName, SUM([Order Details].UnitPrice) as TotalPrice
    /// 	//	FROM Customers
    /// 	//		LEFT OUTER JOIN Orders ON Orders.CustomerID = Customers.CustomerID
    /// 	//		LEFT OUTER JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderID
    /// 	//	WHERE
    /// 	//		-- Orders made in 1997.
    /// 	//		Orders.OrderDate BETWEEN '1997-01-01' AND '1997-12-31'
    /// 	//
    /// 	//		-- Sub-query. Customers who spent less than $100 in 1996.
    /// 	//		AND Customers.CustomerID IN
    /// 	//		(
    /// 	//			SELECT Customers.CustomerID
    /// 	//			FROM Customers
    /// 	//				LEFT OUTER JOIN Orders ON Orders.CustomerID = Customers.CustomerID
    /// 	//				LEFT OUTER JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderID
    /// 	//			WHERE
    /// 	//				Orders.OrderDate BETWEEN '1996-01-01' AND '1996-12-31'
    /// 	//			GROUP BY Customers.CustomerID
    /// 	//			HAVING SUM([Order Details].UnitPrice) &lt; 100
    /// 	//		)
    /// 	//
    /// 	//	GROUP BY Customers.CompanyName
    /// 	//
    /// 	//	-- Customers who spent more than $500.
    /// 	//	HAVING SUM([Order Details].UnitPrice) > 500
    /// 	//	ORDER BY Customers.CompanyName
    /// 
    /// 	CustomersMeta customers = new CustomersMeta();
    /// 	OrdersMeta orders = new OrdersMeta();
    /// 	OrderDetailsMeta details = new OrderDetailsMeta();
    /// 
    /// 	SelectStatement select = new SelectStatement(customers);
    /// 	select.SelectList.Add(customers.CompanyName);
    /// 	select.SelectList.Add(AggregateFunctionFactory.Sum(details.UnitPrice, false, "TotalPrice"));
    /// 	select.Relations.Add(customers, orders, orders.CustomerID);
    /// 	select.Relations.Add(orders, details, details.OrderID);
    /// 
    ///		// Orders made in 1997.
    /// 	select.Where.Add(PredicateFactory.Between(orders.OrderDate, new DateTime(1997,1,1), new DateTime(1997,12,31)));
    /// 
    /// 	// Sub-query. Customers who spent less than $100 in 1996.
    /// 	SelectStatement badIn96 = new SelectStatement(customers);
    /// 	badIn96.SelectList.Add(customers.CustomerID);
    /// 	badIn96.Relations.Add(customers, orders, orders.CustomerID);
    /// 	badIn96.Relations.Add(orders, details, details.OrderID);
    /// 	badIn96.Where.Add(PredicateFactory.Between(orders.OrderDate, new DateTime(1996,1,1), new DateTime(1996,12,31)));
    /// 	badIn96.GroupBy.Fields.Add(customers.CustomerID);
    /// 	badIn96.Having.Add(PredicateFactory.Compare(AggregateFunctionFactory.Sum(details.UnitPrice, false, "TotalPrice"), "&lt;", 100));			
    /// 	select.Where.And(PredicateFactory.In(customers.CustomerID, badIn96));
    /// 
    /// 	select.GroupBy = new GroupByClause(select.SelectList);	
    /// 	// Customers who spent more than $500.
    /// 	select.Having.Add(PredicateFactory.Compare(AggregateFunctionFactory.Sum(details.UnitPrice, false, "TotalPrice"), ">", 500));
    /// 	select.OrderBy.Add(customers.CompanyName, true);
    /// 
    /// 	return select.Execute();
    /// }
    /// </code>
    /// </para>
    /// </example>
    #endregion
    [Serializable]
    public sealed class SelectStatement : SqlStatementBase
    {
        #region Members.

        string alias;

        bool distinct;
        int top = -1;
        SelectItemCollection selectList = new SelectItemCollection();
        RelationBucket relations = new RelationBucket();
        SearchCondition filter = new SearchCondition();
        GroupByClause groupBy = new GroupByClause();
        SearchCondition having = new SearchCondition();
        OrderByClause sorter = new OrderByClause();

        #endregion

        #region Constructors.

        /// <summary>
        /// Creates a new select statements that retrieves rows and columns from the given table.
        /// </summary>
        /// <param name="fromTable">Initial table in the FROM clause.</param>
        public SelectStatement(IDbTable fromTable)
            : base(fromTable)
        {
        }

        /// <summary>
        /// Creates a new select statements that retrieves rows and columns from the given table.
        /// </summary>
        /// <param name="fromTable">Initial table in the FROM clause.</param>
        /// <param name="selectList">Item(s) to fetch. Additional items may be specified in <see cref="SelectList"/> collection.</param>
        public SelectStatement(IDbTable fromTable, DbColumnCollection selectList)
            : base(fromTable)
        {
            this.selectList.Add(selectList);
        }

        /// <summary>
        /// Creates a new select statements that retrieves rows and columns from the given table.
        /// </summary>
        /// <param name="fromTable">Initial table in the FROM clause.</param>
        /// <param name="selectList">Item(s) to fetch. Additional items may be specified in <see cref="SelectList"/> collection.</param>
        public SelectStatement(IDbTable fromTable, params IDbColumn[] selectList)
            : base(fromTable)
        {
            this.selectList.Add(selectList);
        }

        /// <summary>
        /// Creates a new select statements that retrieves rows and columns from the given table.
        /// </summary>
        /// <param name="fromTable">Initial table in the FROM clause.</param>
        /// <param name="selectList">Item(s) to fetch. Additional items may be specified in <see cref="SelectList"/> collection.</param>
        public SelectStatement(IDbTable fromTable, Function selectList)
            : base(fromTable)
        {
            this.selectList.Add(selectList);
        }

        #endregion

        private IDbTable fromTable
        {
            get { return this.TargetTable; }
        }

        #region Methods.

        /// <summary>
        /// Renders SELECT statement.
        /// </summary>
        /// <param name="dbms">Target DBMS.</param>
        /// <param name="output">StringBuilder to which SQL is appended.</param>
        /// <param name="parameters">SQL parameter collection to which the object's and its children's
        /// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
        public override void Render(DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            ISelectRenderer renderer = DbmsComponentFactory.GetComponent<ISelectRenderer>(dbms);
            renderer.Render(this, dbms, output, parameters);
        }

        /// <summary>Retrieves data.</summary>
        /// <returns>Data.</returns>
        public DataTable Execute()
        {
            DbParameterCollection parameters = new DbParameterCollection();
            StringBuilder cmdText = new StringBuilder();
            Render(this.ConnectionProvider.DBMS, cmdText, parameters);

            string command = cmdText.ToString();
            this.LastExecutedCommandInfo = new CommandExecutionStatistics(command);
            DataTable data = DbUtil.ExecuteQuery(this.ConnectionProvider, command, parameters, CommandType.Text, this.fromTable.Alias);
            this.LastExecutedCommandInfo.StopTime();
            return data;
        }

        /// <summary>Retrieves data. Uses given connection provider.</summary>
        /// <param name="conn">Connection provider.</param>
        /// <returns>Data.</returns>
        public DataTable Execute(IConnectionProvider conn)
        {
            this.ConnectionProvider = conn;
            return Execute();
        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
        /// </summary>
        /// <returns>The first column of the first row in the result set, or a <b>null</b> reference if the result set is empty.</returns>
        public object ExecuteScalar()
        {
            DataTable data = Execute();
            object retval;
            if ((data.Rows.Count > 0) && (data.Rows[0][0] != DBNull.Value))
                retval = data.Rows[0][0];
            else
                retval = null;

            return retval;
        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
        /// </summary>
        /// <param name="conn">Connection provider.</param>
        /// <returns>The first column of the first row in the result set, or a <b>null</b> reference if the result set is empty.</returns>
        public object ExecuteScalar(IConnectionProvider conn)
        {
            this.ConnectionProvider = conn;
            return ExecuteScalar();
        }

        #endregion

        #region Properties.

        /// <summary>
        /// Gets or sets query alias. Has no effect on actual select statement but some other statements use it.
        /// Eg. if a sub-query is used as a select item in the select list than it should have an alias.
        /// </summary>
        public string Alias
        {
            get { return this.alias; }
            set { this.alias = value; }
        }

        /// <summary>
        /// Gets or sets the value which specifies that only unique rows can appear in the result set.
        /// </summary>
        /// <remarks><para><b>Performance.</b> Be careful when using DISTINCT if retrieving a lot of large data 
        /// as the database performance may be considerably degraded.</para>
        /// <para><b>Restrictions.</b> Some RDBMSs don't support DISTINCT for all field types. Eg. the text, 
        /// ntext, or image data type cannot be selected as DISTINCT on SQL Server 7 and 2000.</para>
        /// <para><b>NULL values.</b> For the DISTINCT keyword, null values are considered to be duplicates 
        /// of each other. When DISTINCT is included in a SELECT statement, only one NULL is returned 
        /// in the results, regardless of how many null values are encountered.</para></remarks>
        public bool Distinct
        {
            get { return this.distinct; }
            set { this.distinct = value; }
        }

        /// <summary>
        /// Gets or sets the value that specifies the maximum number of rows that are retrieved.
        /// Has no effect if it is set to zero or a negative value; or if the target DBMS doesn't support it.
        /// </summary>
        public int Top
        {
            get { return this.top; }
            set { this.top = value; }
        }

        /// <summary>
        /// Gets or sets the list of items to be retrieved by the SelectStatement.
        /// </summary>
        public SelectItemCollection SelectList
        {
            get { return this.selectList; }
            set { this.selectList = value; }
        }

        /// <summary>
        /// Gets the first table in the FROM clause.
        /// </summary>
        public IDbTable FromTable
        {
            get { return this.fromTable; }
        }

        /// <summary>
        /// Gets or sets the <see cref="RelationBucket"/> that contains the relations used by this statement.
        /// Required when the SelectStatement retrieves columns from multiple tables.
        /// </summary>
        public RelationBucket Relations
        {
            get { return this.relations; }
            set { this.relations = value; }
        }

        /// <summary>
        /// Gets or sets the <see cref="SearchCondition"/> which restricts the rows returned.
        /// </summary>
        public SearchCondition Where
        {
            get { return this.filter; }
            set { this.filter = value; }
        }

        /// <summary>
        /// Gets or sets the <see cref="GroupByClause"/> which defines groups into which output rows are to be placed.
        /// </summary>
        public GroupByClause GroupBy
        {
            get { return this.groupBy; }
            set { this.groupBy = value; }
        }

        /// <summary>
        /// Gets or sets a <see cref="SearchCondition"/> for a group or an aggregate. 
        /// </summary>
        /// <remarks>HAVING is usually used with the GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.</remarks>
        public SearchCondition Having
        {
            get { return this.having; }
            set { this.having = value; }
        }

        /// <summary>
        /// Gets or sets the <see cref="OrderByClause"/> which specifies the sort for the result set.
        /// </summary>
        public OrderByClause OrderBy
        {
            get { return this.sorter; }
            set { this.sorter = value; }
        }

        #endregion

        #region FillCollection.

        /// <summary>Executes a query and directly fills given collection with objects build from retrieved data.</summary>
        /// <param name="destination">A collection to fill with objects built from data.</param>
        /// <param name="objectBuilder">Builds objects directly from retrieved data.</param>    
        /// <remarks><para>The method uses provided converter to directly build specified object type from data contained in 
        /// <see cref="IDataReader"/> objects, thus skipping the need to create intermediate data structures such as <see cref="DataTable"/>
        /// and <see cref="DataSet"/> objects. This can increase performance and reduce memory consumption when working with very 
        /// large results sets.</para>
        /// <example><para>The following example demonstrates the fastest way to fetch and initialize entity collection:<code>
        /// EmployeesMeta employees = new EmployeesMeta();
        /// SelectStatement select = new SelectStatement(employees, employees.Columns);
        /// var allEmployees = new EntityCollection&lt;EmployeesEntity, EmployeesMeta&gt;();
        /// select.FillCollection(allEmployees, employees.NewEntityReader());
        /// </code></para>
        /// <para>Besides entity collections, you can also fill any custom collections or data structures by implementing your own <see cref="IObjectReader"/> objects.
        /// The following example demonstrates the fastest way to fetch all values used in a single column of a table using custom developed object filler:<code>
        /// //Fetch all values from OrderID column of Order Details table.
        /// public List&lt;int&gt; FillOrderIdCollection()
        /// {
        ///     OrderDetailsMeta details = new OrderDetailsMeta();
        ///     SelectStatement select = new SelectStatement(details, details.OrderID);
        ///     // Operation is slightly faster if we can estimate the number of items that the target collection will contain.
        ///     var allItems = new List&lt;int&gt;(2200);
        ///     select.FillCollection(allItems, new SingleFieldReader());
        ///     return allItems;
        /// }
        /// 
        /// //Fills collection with primitive types.
        /// public class SingleFieldReader : IObjectReader
        /// {
        ///     public void FillCollection(IDataReader openedDataReader, IList collection)
        ///     {
        ///         while (openedDataReader.Read())
        ///         {
        ///             object value = openedDataReader.GetValue(0);
        ///             collection.Add(value);
        ///         }
        ///     }
        /// }
        /// </code></para></example></remarks>
        public void FillCollection(IList destination, IObjectReader objectBuilder)
        {
            DbParameterCollection parameters = new DbParameterCollection();
            StringBuilder cmdText = new StringBuilder();
            Render(this.ConnectionProvider.DBMS, cmdText, parameters);

            string command = cmdText.ToString();
            this.LastExecutedCommandInfo = new CommandExecutionStatistics(command);
            DbUtil.ExecuteReader(this.ConnectionProvider, destination, objectBuilder, command, parameters, CommandType.Text);
            this.LastExecutedCommandInfo.StopTime();
        }

        /// <summary>Executes a query and directly fills given collection with objects build from retrieved data.</summary>
        /// <param name="conn">Connection provider.</param>
        /// <param name="destination">A collection to fill with objects built from data.</param>
        /// <param name="objectBuilder">Builds objects directly from retrieved data.</param>    
        /// <remarks><para>The method uses provided converter to directly build specified object type from data contained in 
        /// <see cref="IDataReader"/> objects, thus skipping the need to create intermediate data structures such as <see cref="DataTable"/>
        /// and <see cref="DataSet"/> objects. This can increase performance and reduce memory consumption when working with very 
        /// large results sets.</para>
        /// <example><para>The following example demonstrates the fastest way to fetch and initialize entity collection:<code>
        /// EmployeesMeta employees = new EmployeesMeta();
        /// SelectStatement select = new SelectStatement(employees, employees.Columns);
        /// var allEmployees = new EntityCollection&lt;EmployeesEntity, EmployeesMeta&gt;();
        /// select.FillCollection(allEmployees, employees.NewEntityReader());
        /// </code></para>
        /// <para>Besides entity collections, you can also fill any custom collections or data structures by implementing your own <see cref="IObjectReader"/> objects.
        /// The following example demonstrates the fastest way to fetch all values used in a single column of a table using custom developed object filler:<code>
        /// //Fetch all values from OrderID column of Order Details table.
        /// public List&lt;int&gt; FillOrderIdCollection()
        /// {
        ///     OrderDetailsMeta details = new OrderDetailsMeta();
        ///     SelectStatement select = new SelectStatement(details, details.OrderID);
        ///     // Operation is slightly faster if we can estimate the number of items that the target collection will contain.
        ///     var allItems = new List&lt;int&gt;(2200);
        ///     select.FillCollection(allItems, new SingleFieldReader());
        ///     return allItems;
        /// }
        /// 
        /// //Fills collection with primitive types.
        /// public class SingleFieldReader : IObjectReader
        /// {
        ///     public void FillCollection(IDataReader openedDataReader, IList collection)
        ///     {
        ///         while (openedDataReader.Read())
        ///         {
        ///             object value = openedDataReader.GetValue(0);
        ///             collection.Add(value);
        ///         }
        ///     }
        /// }
        /// </code></para></example></remarks>
        public void FillCollection(IConnectionProvider conn, IList destination, IObjectReader objectBuilder)
        {
            this.ConnectionProvider = conn;
            FillCollection(destination, objectBuilder);
        }

        #endregion
    }
}
